import pymysql
import pandas as pd
from sqlalchemy import create_engine, text

# 使用python连接mysql数据库，并对数据库进行添加数据的方法
# 创建连接，数据库主机地址 数据库用户名称 密码 数据库名 数据库端口 数据库字符集编码
conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password='root',
                       database='notebook',
                       port=3306,
                       charset='utf8')
print("连接成功")

# 创建游标
cursor = conn.cursor()

MYSQL_HOST = '127.0.0.1'
MYSQL_PORT = '3306'
MYSQL_USER = 'root'
MYSQL_PASSWORD = 'root'
MYSQL_DB = 'notebook'
engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8'
                       % (MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, MYSQL_DB))


def selectdata():
    sql = "SELECT * FROM procfiles"
    df = pd.read_sql(text(sql), con=engine.connect())  # 读取mysql数据至df
    return df


# 添加一条数据数据
def insertdata1(sql):
    print("sql是什么：", sql)
    # insert_emp_sql = "INSERT into procfiles(PROC_NAME,FILE_NAME,FILE_PATH,CREATE_DATE) VALUES ('模拟车灯测试','Untitled7.ipynb','http://dz-2404-2:8888/notebooks/PPP/Untitled7.ipynb','2023-04-22')"
    # 执行语句
    cursor.execute(sql)
    # 提交数据
    conn.commit()


# 批量添加数据
def insertdata2():
    insert_emp_sql = ""

    # 插入10条数据0-9
    for i in range(10):
        uname = '高少少' + str(i)
        age = 30 + i

        ins_sql = insert_emp_sql.format(uname, age)
        cursor.execute(ins_sql)
        conn.commit()


# 删除数据
def deletedata(id):
    delete_emp_sql = "delete from empdb.employee where eid={}"
    del_sql = delete_emp_sql.format(id)
    cursor.execute(del_sql)
    conn.commit()


# 更改数据
def updatadata():
    updata_emp_sql = "update empdb.employee set age=66 where eid = 26"
    cursor.execute(updata_emp_sql)
    conn.commit()


# 关闭游标跟连接
def closeconn():
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()